IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[sp_Report_HangBanTrongKhoangNgayTheoNhomHang]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[sp_Report_HangBanTrongKhoangNgayTheoNhomHang]
GO


CREATE PROC [sp_Report_HangBanTrongKhoangNgayTheoNhomHang]
(
	@Ma_chi_nhanh 		INT,
	@Tu_ngay 			DATETIME,
	@Den_ngay 			DATETIME
)	
AS
BEGIN
	/* 
	* declare bien @Tu_ngay2 va @Den_ngay2 de chuyen ngay @Tu_ngay va @Den_ngay thanh dang dd/mm/yyyy kieu nvarchar
	*/
	DECLARE @Tu_ngay2 NVARCHAR(10)
	DECLARE @Den_ngay2 NVARCHAR(10)
	
	SET @Tu_ngay2 = CONVERT(NVARCHAR, DAY(@Tu_ngay)) + '/' + CONVERT(NVARCHAR, MONTH(@Tu_ngay)) + '/' + CONVERT(NVARCHAR, YEAR(@Tu_ngay))
	SET @Den_ngay2 = CONVERT(NVARCHAR, DAY(@Den_ngay)) + '/' + CONVERT(NVARCHAR, MONTH(@Den_ngay)) + '/' + CONVERT(NVARCHAR, YEAR(@Den_ngay))
	
	/*
	* lay ma hang hoa, ten hang hoa, ten cua nhom hang, 
	* tinh so luong hang hoa ban ra dua theo hang hoa va nhom hang,
	* tinh so luong hang hoa khach hang tra lai dua theo hang hoa va nhom hang,
	* tu ngay nao den ngay nao tai cua hang nao
	*/
	/*
	* loai nhap xuat = 0 : xuat hang de ban cho khach
	* loai nhap xuat = 4 : nhap hang khach tra lai
	*/
	SELECT 
		C.Ma_hang AS 'Ma_hang', 
		C.Ten AS 'Ten_hang', 
		D.Ten AS 'Ten_nhom_hang',
		SUM(CASE WHEN A.Loai_nhap_xuat = 0 THEN B.So_luong ELSE 0 END) AS 'So_luong_ban', 
		SUM(CASE WHEN A.Loai_nhap_xuat = 4 THEN B.So_luong ELSE 0 END) AS 'So_luong_tra',
		'Tu_ngay' = @Tu_ngay2,
		'Den_ngay' = @Den_ngay2
	FROM PhieuNhapXuat AS A INNER JOIN ChiTietNhapXuat AS B ON A.id = B.Ma_phieu_nhap_xuat
		INNER JOIN HangHoa AS C ON B.Ma_hang_hoa = C.id
		INNER JOIN NhomHang AS D ON C.Ma_nhom_hang = D.id
		INNER JOIN ThanhVien AS E ON A.Ma_nhan_vien = E.id		
	WHERE E.Ma_chi_nhanh = @Ma_chi_nhanh AND CONVERT(NVARCHAR,B.Ngay_cap_nhat_cuoi,111) BETWEEN @Tu_ngay AND @Den_ngay
	GROUP BY C.Ma_hang, C.Ten, D.Ten
END


